package com.lovestudying.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.Map.Entry;

import com.lovestudying.dao.TeacherDao;
import com.lovestudying.entity.Course;
import com.lovestudying.entity.Teacher;
import com.lovestudying.util.DBUtil;
import com.lovestudying.util.DBUtil_Pro;
import com.lovestudying.util.PageInfo;

public class TeacherDaoImpl implements TeacherDao {

	private static final ResultSet Doquery = null;

	@Override
	public Integer InsertTeacher(Map<String, Object> Value_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("INSERT INTO  Teacher (");
		// values参数列表
		Vector<Object> params = new Vector<>();
		int i = 0;
		// 根据Map中的情况构建 Insert语句的条件
		for (Entry<String, Object> element : Value_Param.entrySet()) {
			sql.append(element.getKey());
			params.add(element.getValue());
			i++;
			if (i < Value_Param.size()) {
				sql.append(",");
			}
		}
		sql.append(") VALUES (");
		for (int j = 0; j < params.size(); j++) {
			sql.append("?");
			if (j < params.size() - 1) {
				sql.append(",");
			}
		}
		sql.append(")");
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	@Override
	public Integer DeleteTeacher(Map<String, Object> Where_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("DELETE FROM Teacher");
		// Where参数列表
		Vector<Object> params = new Vector<>();
		// 根据Map中的情况构建 查询语句的条件
		if (Where_Param != null && Where_Param.size() != 0) {
			sql.append(" WHERE");
			int i = 0;
			for (Entry<String, Object> element : Where_Param.entrySet()) {
				sql.append(" " + element.getKey() + " = ?");
				params.add(element.getValue());
				i++;
				if (i < Where_Param.size()) {
					sql.append(" and");
				}
			}
		}
		// System.out.println(sql.toString());
		// System.out.println(params);
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	@Override
	public Integer UpdateTeacher(Map<String, Object> Set_Param, Map<String, Object> Where_Param) {
		// 创建sql语句
		StringBuffer sql = new StringBuffer("UPDATE Teacher SET");
		// 参数列表
		Vector<Object> params = new Vector<>();

		int i = 0;
		// 根据Map中的情况构建 Insert语句的条件
		for (Entry<String, Object> element : Set_Param.entrySet()) {
			sql.append(" " + element.getKey() + " = ?");
			params.add(element.getValue());
			i++;
			if (i < Set_Param.size()) {
				sql.append(",");
			}
		}
		sql.append(" WHERE");
		int j = 0;
		for (Entry<String, Object> element : Where_Param.entrySet()) {
			sql.append(" " + element.getKey() + " = ?");
			params.add(element.getValue());
			j++;
			if (j < Where_Param.size()) {
				sql.append(" and");
			}
		}
		return DBUtil_Pro.doUpdate(sql.toString(), params);
	}

	@Override
	public PageInfo<Teacher> findTeacherUniCourse(Map<String, Object> Where_Param, Map<String, Object> OrdeBy_Param,
			Integer Lmt_pageSize, Integer Lmt_pageNum) {
		List<Teacher> list = new Vector<>();
		PageInfo<Teacher> TeacherPage;
		ResultSet rs = null;
		// 创建sql语句
		StringBuffer sql = new StringBuffer(
				"SELECT DISTINCT teacher.teacher_id ,teacher_name,teacher_preview,teacher_pic FROM Teacher teacher LEFT JOIN Course course ON teacher.teacher_id = course.teacher_id");
		// Where参数列表
		Vector<Object> params = new Vector<>();
		// 根据Map中的情况构建 查询语句的条件
		if (Where_Param != null && Where_Param.size() != 0) {
			sql.append(" WHERE");
			int i = 0;
			for (Entry<String, Object> element : Where_Param.entrySet()) {
				if (element.getKey().contains("teacher_name") || element.getKey().contains("course_title")) {
					sql.append(" " + element.getKey() + " like ?");
					params.add("%" + element.getValue() + "%");
					i++;
				} else {
					sql.append(" " + element.getKey() + " = ?");
					params.add(element.getValue());
					i++;
				}

				if (i < Where_Param.size()) {
					sql.append(" and");
				}
			}
		}
		int total = 0;
		try {
			ResultSet last = DBUtil_Pro.doQuery(sql.toString(), params);

			last.last();
			total = last.getRow();
			// System.out.println(count);
		} catch (SQLException e1) {
			// TODO 自动生成的 catch 块
			e1.printStackTrace();
		}

		// 添加OrderBy参数
		if (OrdeBy_Param != null && OrdeBy_Param.size() != 0) {
			sql.append(" ORDER BY");
			int i = 0;
			// 遍历Map
			for (Entry<String, Object> element : OrdeBy_Param.entrySet()) {
				sql.append(" " + element.getKey());
				// 做升降序设置，键的值是null则是默认的ASC，不为null则添加DESC
				if (element.getValue() != null) {
					sql.append(" DESC");
				}
				i++;
				if (i < OrdeBy_Param.size()) {
					sql.append(",");
				}
			}
		}
		// 添加Limit参数
		if (Lmt_pageNum != null && Lmt_pageSize != null) {
			sql.append(" LIMIT ?,?");
			params.add((Lmt_pageNum - 1) * Lmt_pageSize);
			params.add(Lmt_pageSize);
		}

		// 执行SQL语句
		rs = DBUtil_Pro.doQuery(sql.toString(), params);
		try {
			while (rs.next()) {
				Teacher teacher = new Teacher(rs.getString("teacher_id"), rs.getString("teacher_name"),
						rs.getString("teacher_preview"), rs.getString("teacher_pic"));
				list.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		TeacherPage = new PageInfo<>(list, Lmt_pageNum, Lmt_pageSize, total);
		// System.out.println(sql.toString());
		return TeacherPage;
	}

	@Override
	public int deleteById(String teacher_id) {
		// TODO Auto-generated method stub
		return DBUtil.update("delete from teacher where teacher_id = ? ", teacher_id);
	}

	@Override
	public int updateById(Teacher teacher) {

		return DBUtil.update("update teacher set teacher_preview = ?, teacher_pic = ? where teacher_id = ? ",
				teacher.getTeacher_preview(), teacher.getTeacher_pic(),teacher.getTeacher_id());
	}

	@Override
	public List<Teacher> findByCondition(Map<String, String> params) {
		List<Teacher> list = new ArrayList<>();
		ResultSet doQuery = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select A.teacher_id").append(",A.teacher_name").append(",A.teacher_preview").append(",A.teacher_pic")
				.append(",B.course_id").append(",B.course_title").append(" from teacher A")
				.append(" inner join course B").append(" on A.teacher_id = B.teacher_id");

		if ("".equals(params.get("teacherKey"))) {
			doQuery = DBUtil.doQuery(sb.toString());
		}
		if (!"".equals(params.get("teacherKey"))) {
			sb.append(" where course_title like ?");
			doQuery = DBUtil.doQuery(sb.toString(), "%" + params.get("teacherKey") + "%");
		}
		try {
			while (doQuery.next()) {
				String teacher_id = doQuery.getString("teacher_id");
				String teacher_name = doQuery.getString("teacher_name");
				String teacher_preview = doQuery.getString("teacher_preview");
				String teacher_pic = doQuery.getString("teacher_pic");

				Teacher teacher = new Teacher();
				teacher.setTeacher_id(teacher_id);
				teacher.setTeacher_name(teacher_name);
				teacher.setTeacher_preview(teacher_preview);
				teacher.setTeacher_pic(teacher_pic);

				list.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}
